﻿using System;
using System.Collections;
using NHibernate;
using NHibernate.Cfg;
using System.Collections.Generic;

namespace Business
{
    public class Voucher
    {
        private ISessionFactory _sessionFactory;
        public Voucher()
        {
            _sessionFactory = DataObjects.Service.SessionFactory.GetInstance().GetNHibernateFactory();
        }
        
        public ArrayList List()
        {
            ArrayList arrRet = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                arrRet = (ArrayList)session.CreateCriteria(typeof(DataObjects.Voucher)).List();
                tx.Commit();
            }
            return arrRet;
        }

        public ArrayList GetListByStatus(string status)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "select * from Voucher where Status = ?";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.Voucher));
                query.SetString(0, status);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public ArrayList GetListQuickSearch(string condition)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "SELECT VOUCHER.* FROM VOUCHER INNER JOIN CUSTOMER ON VOUCHER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID WHERE (CUSTOMER.CUSTOMER_NAME LIKE N'%" + condition + "%')";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.Voucher));
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public ArrayList SearchVoucher(object[] oParam)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "exec sp_SearchVoucher ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.Voucher));
                query.SetString(0, oParam[0].ToString());                   //Voucher_No
                query.SetString(1, oParam[1].ToString());                   //Employee_Id
                query.SetString(2, oParam[2].ToString());                   //Full_Name
                query.SetString(3, oParam[3].ToString());                   //Provider_Id
                query.SetString(4, oParam[4].ToString());                   //Provider_Name
                query.SetString(5, oParam[5].ToString());                   //Customer_Id
                query.SetString(6, oParam[6].ToString());                   //Customer_Name
                query.SetDateTime(7, Convert.ToDateTime(oParam[7]));        //Date_From         ToString("yyyy-MM-dd hh:mm:ss")
                query.SetDateTime(8, Convert.ToDateTime(oParam[8]));        //Date_To
                query.SetDouble(9, double.Parse(oParam[9].ToString()));     //SumBuyVndFrom
                query.SetDouble(10, double.Parse(oParam[10].ToString()));   //SumBuyVndTo
                query.SetDouble(11, double.Parse(oParam[11].ToString()));   //SumSaleVndFrom
                query.SetDouble(12, double.Parse(oParam[12].ToString()));   //SumSaleVndTo
                query.SetString(13, oParam[13].ToString());                 //Status

                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public DataObjects.Voucher GetObjectByPK(string voucher_no)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("select top 1 * from Voucher where Voucher_No = ?").AddEntity(typeof(DataObjects.Voucher));
                query.SetString(0, voucher_no);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                if (arrTemp.Count > 0)
                {
                    return (DataObjects.Voucher)arrTemp[0];
                }
                return null;
            }
        }

        public int GetMaxID(string cond)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("select top 1 * from Voucher where Voucher_No like N'" + cond + "%' order by Voucher_No desc").AddEntity(typeof(DataObjects.Voucher));
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                DataObjects.Voucher oVou;

                if (arrTemp.Count > 0)
                {
                    oVou = (DataObjects.Voucher)arrTemp[0];
                    if (oVou != null)
                    {
                        string no = oVou.VoucherNo.Replace(cond, "");
                        return int.Parse(no);
                    }
                }
                return 0;
            }
        }

        //code insert - update - delete 
        public void Insert(DataObjects.Voucher oVoucher)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Save(oVoucher);
                tx.Commit();			        
            }
        }
        public void Update(DataObjects.Voucher oVoucher)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Update(oVoucher);
                tx.Commit();		        
            }
        }
        public void DeleteByPK(string voucher_no)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                DataObjects.Voucher oVoucher = (DataObjects.Voucher)session.Load(typeof(DataObjects.Voucher), voucher_no);
                session.Delete(oVoucher);
                tx.Commit();
            }
        }
    }
}
